﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;

public partial class Orders : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();

    protected void Page_Load(object sender, EventArgs e)
    {
        con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

        if (this.IsPostBack == false)
        {
            try
            {
                gvOrders.AutoGenerateSelectButton = true;
                gvOrders.AllowPaging = true;
                gvOrders.PageSize = 20;
                LoadOrders();
                txtOrderDate.Text = DateTime.Now.ToString("yyyy/MM/dd");
                txtRequiredDate.Text = DateTime.Now.ToString("yyyy/MM/dd");
                txtShippedDate.Text = DateTime.Now.ToString("yyyy/MM/dd");
                txtSearch.Text = DateTime.Now.ToString("yyyy/MM/dd");
                btnDelete.Enabled = false;
                btnUpdate.Enabled = false;
                btnReload.Enabled = false;
                btnSeeDetail.Enabled = false;
                btnAdd.Enabled = true;
                btnNew.Enabled = true;
                Session["isSelectedOrders"] = false;
            }
            catch (Exception)
            {
                Response.Write("<script>alert('" + "Fails Page! F5 to Reload" + "')</script>");
            }
        }
        bool tmp = (bool)Session["isSelectedOrders"];
        //o day pa xet la dung roi ha, nhung con thieu
        if (tmp)
        {
            btnDelete.Enabled = true;
            btnUpdate.Enabled = true;
            btnSeeDetail.Enabled = true;

            btnAdd.Enabled = false;
        }
        else
        {
            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
            btnAdd.Enabled = true;
        }


    }

    void LoadOrders()
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM Sales.Orders ORDER BY orderdate DESC";

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            gvOrders.DataSource = dt;
            gvOrders.DataBind();

            getDataSource_CustomerID();
            getDataSource_EmployeesID();
            getDatasource_ShipperID();
        }
        catch (Exception)
        {

            Response.Write("<script>alert('" + "Fails Page! F5 to Reload" + "')</script>");
        }


    }


    void AddOrder()
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "dbo.sp_Orders_Insert";

            SqlParameter param = new SqlParameter("@custid", SqlDbType.Int);
            param.Value = DDLCustID.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = DDLEmpID.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@orderdate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtOrderDate.Text.Trim());
            cmd.Parameters.Add(param);

            param = new SqlParameter("@requireddate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtRequiredDate.Text.Trim());
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
            if (txtShippedDate.Text.Trim() != "")
            {
                param.Value = DateTime.Parse(txtShippedDate.Text.Trim());
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipperid", SqlDbType.Int);
            param.Value = DDLShipperID.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@freight", SqlDbType.Money);
            if (txtFreight.Text.Trim() != "")
            {
                param.Value = decimal.Parse(txtFreight.Text.Trim());
            }
            else
            {
                param.Value = 0;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipname", SqlDbType.NVarChar, 40);
            param.Value = txtShipName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipaddress", SqlDbType.NVarChar, 60);
            param.Value = txtShipAddress.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcity", SqlDbType.NVarChar, 15);
            param.Value = txtShipCity.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipregion", SqlDbType.NVarChar, 15);
            if (txtShipRegion.Text.Trim() != "")
            {
                param.Value = txtShipRegion.Text.Trim();
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar, 10);
            if (txtShipPostalCode.Text.Trim() != "")
            {
                param.Value = txtShipPostalCode.Text.Trim();
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcountry", SqlDbType.NVarChar, 15);
            param.Value = DDLShipCountry.Text.Trim();
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script>alert('Add successful. Add some details');window.location='OrderDetails.aspx'</script>");
            // call detail table
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT MAX(orderid) FROM Sales.Orders";
            SqlDataReader dr = cmd.ExecuteReader();

            dr.Read();
            Session["orderid"] = dr[0];


        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            con.Close();
        }
    }

    void Initdata()
    {

        txtOrderID.Text = "";
        txtShipAddress.Text = "";
        txtShipCity.Text = "";
        DDLShipCountry.Text = "";
        txtShipName.Text = "";
        txtShipRegion.Text = "";
        txtShipPostalCode.Text = "";
        DDLCustID.SelectedIndex = 0;
        DDLEmpID.SelectedIndex = 0;
        DDLShipperID.SelectedIndex = 0;
        txtOrderDate.Text = DateTime.Now.ToString("yyyy/MM/dd");
        txtRequiredDate.Text = DateTime.Now.ToString("yyyy/MM/dd");
        txtShippedDate.Text = DateTime.Now.ToString("yyyy/MM/dd");
        txtFreight.Text = "";
        Session["isSelectedOrders"] = false;
        btnDelete.Enabled = false;
        btnUpdate.Enabled = false;
        btnAdd.Enabled = true;
        
        btnSeeDetail.Enabled = false;
    }

    void UpdateOrders()
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "dbo.sp_Orders_Update";

            SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
            param.Value = txtOrderID.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@custid", SqlDbType.Int);
            param.Value = DDLCustID.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = DDLEmpID.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@orderdate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtOrderDate.Text.Trim());
            cmd.Parameters.Add(param);

            param = new SqlParameter("@requireddate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtRequiredDate.Text.Trim());
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
            if (txtShippedDate.Text.Trim() !=  "")
            {
                param.Value = DateTime.Parse(txtShippedDate.Text.Trim());
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipperid", SqlDbType.Int);
            param.Value = DDLShipperID.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@freight", SqlDbType.Money);
            if (txtFreight.Text.Trim() != "")
            {
                param.Value = decimal.Parse(txtFreight.Text.Trim());
            }
            else
            {
                param.Value = 0;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipname", SqlDbType.NVarChar, 40);
            param.Value = txtShipName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipaddress", SqlDbType.NVarChar, 60);
            param.Value = txtShipAddress.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcity", SqlDbType.NVarChar, 15);
            param.Value = txtShipCity.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipregion", SqlDbType.NVarChar, 15);
            if (txtShipRegion.Text.Trim() != "")
            {
                param.Value = txtShipRegion.Text.Trim();
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar, 10);
            if (txtShipPostalCode.Text.Trim() != "")
            {
                param.Value = txtShipPostalCode.Text.Trim();
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcountry", SqlDbType.NVarChar, 15);
            param.Value = DDLShipCountry.Text.Trim();
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script>alert('" + "Update successful" + "')</script>");
        }
        catch (Exception ex)
        {

            throw (ex);
        }
        finally
        {
            con.Close();
        }
    }

    void DeleteOrders()
    {
        try
        {
            // Check if orderDetail is empty
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "dbo.sp_Orders_Delete";

            SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
            param.Value = txtOrderID.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script>alert('" + "Delete successful" + "')</script>");
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            con.Close();
        }
    }

    void SearchOrders()
    {
        try
        {

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            if (DDLSearchBy.SelectedIndex == 0)
            {
                cmd.CommandText = string.Format("SELECT * FROM Sales.Orders WHERE orderdate = '{0}' ORDER BY orderdate DESC", DateTime.Parse(txtSearch.Text.Trim()).ToShortDateString());
            }
            else
            {
                if (DDLSearchBy.SelectedIndex == 1)
                {
                    cmd.CommandText = string.Format("SELECT * FROM Sales.Orders WHERE requireddate = '{0}' ORDER BY orderdate DESC", DateTime.Parse(txtSearch.Text.Trim()).ToShortDateString());
                }
                if (DDLSearchBy.SelectedIndex == 2)
                {
                    cmd.CommandText = string.Format("SELECT * FROM Sales.Orders WHERE shippeddate = '{0}' ORDER BY orderdate DESC", DateTime.Parse(txtSearch.Text.Trim()).ToShortDateString());
                }
            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("Sales.Orders");
            da.Fill(dt);

            gvOrders.DataSource = dt;
            gvOrders.DataBind();
            
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            con.Close();
        }
    }

    void getDataSource_CustomerID()
    {
        try
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT custid, companyname FROM Sales.Customers";
            da = new SqlDataAdapter(cmd);
            da.FillSchema(ds, SchemaType.Source, "Sales.Customers");
            da.Fill(ds, "Sales.Customers");
            DataTable dt = ds.Tables["Sales.Customers"];
            da.Fill(dt);
            DDLCustID.DataSource = dt;
            DDLCustID.DataTextField = "companyname";
            DDLCustID.DataValueField = "custid";
            DDLCustID.DataBind();
        }
        catch (Exception ex)
        {

            throw (ex);
        }
    }

    void getDataSource_EmployeesID()
    {
        try
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT empid, lastname + ' ' + firstname AS fullname FROM HR.Employees";
            da = new SqlDataAdapter(cmd);
            da.FillSchema(ds, SchemaType.Source, "HR.Employees");
            da.Fill(ds, "HR.Employees");
            DataTable dt = ds.Tables["HR.Employees"];
            da.Fill(dt);
            DDLEmpID.DataSource = dt;
            DDLEmpID.DataTextField = "fullname";
            DDLEmpID.DataValueField = "empid";
            DDLEmpID.DataBind();
        }
        catch (Exception ex)
        {

            throw (ex);
        }
    }

    void getDatasource_ShipperID()
    {
        try
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT shipperid, companyname FROM Sales.Shippers";
            da = new SqlDataAdapter(cmd);
            da.FillSchema(ds, SchemaType.Source, "Sales.Shippers");
            da.Fill(ds, "Sales.Shippers");
            DataTable dt = ds.Tables["Sales.Shippers"];
            da.Fill(dt);
            DDLShipperID.DataSource = dt;
            DDLShipperID.DataTextField = "companyname";
            DDLShipperID.DataValueField = "shipperid";
            DDLShipperID.DataBind();
        }
        catch (Exception ex)
        {

            throw (ex);
        }
    }

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        try
        {
            AddOrder();
            LoadOrders();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Can not add" + "')</script>");
        }
    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        Initdata();
    }


    protected void gvOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {

            gvOrders.PageIndex = e.NewPageIndex;
            LoadOrders();
        }
        catch (Exception)
        {
            
            Response.Write("<script>alert('" + "Can not Change Page" + "')</script>");
        }

    }
    protected void gvOrders_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            GridViewRow r = gvOrders.SelectedRow;
            txtOrderID.Text = r.Cells[1].Text;
            DDLCustID.SelectedValue = r.Cells[2].Text;
            DDLEmpID.SelectedValue = r.Cells[3].Text;
            txtOrderDate.Text = DateTime.Parse(r.Cells[4].Text.Trim()).ToString("yyyy/MM/dd");
            txtRequiredDate.Text = DateTime.Parse(r.Cells[5].Text.Trim()).ToString("yyyy/MM/dd");
            txtShippedDate.Text = Server.HtmlDecode(r.Cells[6].Text).Trim() == ""
                ? ""
                : DateTime.Parse(r.Cells[6].Text).ToString("yyyy/MM/dd");
            DDLShipperID.SelectedValue = r.Cells[7].Text;
            txtFreight.Text = r.Cells[8].Text;
            txtShipName.Text = Server.HtmlDecode(r.Cells[9].Text);
            txtShipAddress.Text = Server.HtmlDecode(r.Cells[10].Text);
            txtShipCity.Text = Server.HtmlDecode(r.Cells[11].Text);
            txtShipRegion.Text = Server.HtmlDecode(r.Cells[12].Text);
            txtShipPostalCode.Text = Server.HtmlDecode(r.Cells[13].Text);
            DDLShipCountry.Text = r.Cells[14].Text;
            Session["isSelectedOrders"] = true;
            //o day phai xet truoc khi tra ra cho nguoi dung ak, ok
            //bool tmp = (bool)Session["isSelected"];
            btnDelete.Enabled = true;
            btnUpdate.Enabled = true;
            btnSeeDetail.Enabled = true;

            btnAdd.Enabled = false;
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Can not select" + "')</script>");
        }
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        //try
        //{
            UpdateOrders();
            LoadOrders();
        //}
        //catch (Exception)
        //{

        //    Response.Write("<script>alert('" + "Can not update" + "')</script>");
        //}
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        try
        {
            DeleteOrders();
            LoadOrders();
            Initdata();
        }
        catch (Exception)
        {

            Response.Write("<script>alert('" + "Can not delete" + "')</script>");
        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        try
        {
            Initdata();
            SearchOrders();
            DDLSearchBy.SelectedIndex = 0;
            txtSearch.Text = "";
            btnReload.Enabled = true;
        }
        catch (Exception)
        {

            Response.Write("<script>alert('" + "Can not search" + "')</script>");
        }
    }
    protected void btnReload_Click(object sender, EventArgs e)
    {
        try
        {
            Initdata();
            LoadOrders();
            txtSearch.Text = DateTime.Now.ToString("yyyy/MM/dd");
            btnReload.Enabled = false;
        }
        catch (Exception)
        {

            Response.Write("<script>alert('" + "Can not load" + "')</script>");
        }
    }
    protected void btnSeeDetail_Click(object sender, EventArgs e)
    {
        try
        {
            Session["orderid"] = Int32.Parse(txtOrderID.Text);
            Response.Redirect("OrderDetails.aspx");
        }
        catch (Exception)
        {
            
           Response.Write("<script>alert('" + "Can not call order details" + "')</script>");
        }
    }
}
